package cn.mfeg.servlet;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import cn.mfeg.models.ExpertUser;
import cn.mfeg.models.Project;
import cn.mfeg.services.ExpertUserService;
import cn.mfeg.services.ProjectService;
import cn.mfeg.utils.ExportExcelUtil;
import cn.mfeg.utils.ObjectValidateUtil;

public class ExportEProjectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
	ApplicationContext applicationContext;
	ProjectService projectService;
	ExpertUserService expertUserService;
	public void init() throws ServletException {
		applicationContext = new ClassPathXmlApplicationContext("spring-servlet.xml");
		projectService = (ProjectService) applicationContext.getBean(ProjectService.class);
		expertUserService = (ExpertUserService) applicationContext.getBean(ExpertUserService.class);
	}
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<ExpertUser> expertUsers = expertUserService.selectAllExpertInfo();
		 for(ExpertUser expertUser:expertUsers){
			 List<Project> projects = projectService.selectInfoByIdCard(expertUser.getIdCard());
			 expertUser.setProjects(projects);
		 }
		//创建工作空间
		HSSFWorkbook book = new HSSFWorkbook();
		// 创建一个工作表
		HSSFSheet sheet = book.createSheet("专家项目报表");
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		// 创建一个单元格 设置值
		HSSFCell cel = null;
		cel = row.createCell(0);
		cel.setCellValue("高校代码/名称");
		cel = row.createCell(1);
		cel.setCellValue("用户名");
		cel = row.createCell(2);
		cel.setCellValue("姓名");
		cel = row.createCell(3);
		cel.setCellValue("电话");
		cel = row.createCell(4);
		cel.setCellValue("状态");
		cel = row.createCell(5);
		cel.setCellValue("创建用户");
		cel = row.createCell(6);
		cel.setCellValue("项目名称");
		cel = row.createCell(7);
		cel.setCellValue("项目日期");
		cel = row.createCell(8);
		cel.setCellValue("项目状态");
		cel = row.createCell(9);
		cel.setCellValue("主办单位");
		cel = row.createCell(10);
		cel.setCellValue("项目官网");
		cel = row.createCell(11);
		cel.setCellValue("联系电话");
		cel = row.createCell(12);
		cel.setCellValue("需要专家人数");
		cel = row.createCell(13);
		cel.setCellValue("已有专家人数");
		cel = row.createCell(14);
		cel.setCellValue("优秀专家人数");
		cel = row.createCell(15);
		cel.setCellValue("评价");
		cel = row.createCell(16);
		cel.setCellValue("评语");
		int rowIndex = 1;
		for (ExpertUser e:expertUsers) {
			int size = e.getProjects().size();
			//合并单元格  CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
			if (ObjectValidateUtil.objIsNull(e.getProjects())||e.getProjects().size()==0) {
				CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 5, 16);
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(e.getCollegesCode()+"/"+e.getCollegesName());
				cel = row.createCell(1);
				cel.setCellValue(e.getUsername());
				cel = row.createCell(2);
				cel.setCellValue(e.getName());
				cel = row.createCell(3);
				cel.setCellValue(e.getMobile());
				cel = row.createCell(4);
				cel.setCellValue(e.getStatus());
				cel = row.createCell(5);
				cel.setCellValue("无");
				sheet.addMergedRegion(region);
			}else {
				if (size>1) {
					CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex+size-1, 0, 0);
					CellRangeAddress region2 = new CellRangeAddress(rowIndex, rowIndex+size-1, 1, 1);
					CellRangeAddress region3 = new CellRangeAddress(rowIndex, rowIndex+size-1, 2, 2);
					CellRangeAddress region4 = new CellRangeAddress(rowIndex, rowIndex+size-1, 3, 3);
					CellRangeAddress region5 = new CellRangeAddress(rowIndex, rowIndex+size-1, 4, 4);
					row = sheet.createRow(rowIndex);
					cel = row.createCell(0);
					cel.setCellValue(e.getCollegesCode()+"/"+e.getCollegesName());
					cel = row.createCell(1);
					cel.setCellValue(e.getUsername());
					cel = row.createCell(2);
					cel.setCellValue(e.getName());
					cel = row.createCell(3);
					cel.setCellValue(e.getMobile());
					cel = row.createCell(4);
					cel.setCellValue(e.getStatus());
					sheet.addMergedRegion(region);
					sheet.addMergedRegion(region2);
					sheet.addMergedRegion(region3);
					sheet.addMergedRegion(region4);
					sheet.addMergedRegion(region5);
				}
			}
			for(Project p:e.getProjects()){
				row = sheet.createRow(rowIndex++);
				cel = row.createCell(0);
				cel.setCellValue(e.getCollegesCode()+"/"+e.getCollegesName());
				cel = row.createCell(1);
				cel.setCellValue(e.getUsername());
				cel = row.createCell(2);
				cel.setCellValue(e.getName());
				cel = row.createCell(3);
				cel.setCellValue(e.getMobile());
				cel = row.createCell(4);
				cel.setCellValue(e.getStatus());
				cel = row.createCell(5);
				cel.setCellValue(p.getOrganiser());
				cel = row.createCell(6);
				cel.setCellValue(p.getProjectname());
				cel = row.createCell(7);
				cel.setCellValue(p.getStarttime()+"~"+p.getEndtime());
				cel = row.createCell(8);
				cel.setCellValue(p.getStatus());
				cel = row.createCell(9);
				cel.setCellValue(p.getHostunit());
				cel = row.createCell(10);
				cel.setCellValue(p.getWebsite());
				cel = row.createCell(11);
				cel.setCellValue(p.getPhonenum());
				cel = row.createCell(12);
				cel.setCellValue(p.getExperttotal());
				cel = row.createCell(13);
				cel.setCellValue("XXX");
				cel = row.createCell(14);
				cel.setCellValue("XXX");
				cel = row.createCell(15);
				cel.setCellValue(p.getAppraise());
				cel = row.createCell(16);
				cel.setCellValue(p.getComment());
			}
		}
		 
		ExportExcelUtil.exportExcel(req, resp, book);
	}
}
